from tkinter import *
from tkinter import filedialog
from threading import Thread
from queue import Queue
import openpyxl.workbook
from config import *
from sql import sqlseverDB
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
import tkinter.messagebox as messagebox
import openpyxl,time,os,concurrent.futures

class DataFra9(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='allegro功能', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        DrDDFrame(self).pack(fill=X,padx=3,pady=3)
        CopTaskFrame(self).pack(fill=X,padx=3,pady=3)
        HYFrame(self).pack(fill=X,padx=3,pady=3)

class DrDDFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.rowCount=IntVar()
        self.createWidgets()

    def createWidgets(self):
        
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='ERP订单上传', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, text='订单总表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.rowCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        self.drbtn=Button(f00,text='上传订单',command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green')
        self.drbtn.pack(side=LEFT, padx=2, pady=2)
        self.drbtn2=Button(f00,text='生成任务表',command=lambda:Thread(target=self.start_dr2).start(),fg='#ffffff',bg='green')
        self.drbtn2.pack(side=LEFT, padx=2, pady=2)
        self.drbtn3=Button(f00,text='上传_采购完成需付款',command=lambda:Thread(target=self.start_dr3).start(),fg='#ffffff',bg='green')
        self.drbtn3.pack(side=LEFT, padx=2, pady=2)
    

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.rowCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')

    def start_dr(self):
        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先选择文件')
            return
        
        self.drbtn.config(state=DISABLED)
        new_order_datas=self.get_new_data()
        self.send_count=len(new_order_datas)
        sufb=0
        sbfb=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=1
            for r in new_order_datas:

                futures.append(executor.submit(self.upload_dd,row,r))
                row+=1
            for future in concurrent.futures.as_completed(futures):
                try:
                    res_status=future.result()
                    if res_status:
                        sufb+=1
                    else:
                        sbfb+=1
                except Exception as exc:
                    print(f"线程出错: {exc}")

        print('开始更新速卖通采购信息...')

        sqlstr='''UPDATE AllegroOrder
                    SET 
                        smturl = allproduct.smturl,
                        smtsku1 = allproduct.smtsku1,
                        smtsku2 = allproduct.smtsku2,
                        smtsku3 = allproduct.smtsku3,
                        smtsku4 = allproduct.smtsku4
                    FROM AllegroOrder
                    INNER JOIN allproduct ON AllegroOrder.ChanPinID = allproduct.ChanPinID;
                '''
        aff=self.ms.ExecNoQuery(sqlstr)
        print(f'发布完成,共{self.send_count}个任务,其中成功上传:{sufb}个,失败{sbfb}个,自动填充匹配速卖通采购信息 {aff} 个')
        messagebox.showinfo('提示',f'上传完毕,共{len(new_order_datas)}行数据,其中成功上传:{sufb}个,失败{sbfb}个,自动填充匹配速卖通采购信息 {aff} 个')
        self.drbtn.config(state=NORMAL)
    
    def get_new_data(self,tb_name='AllegroOrder'):

        order_datas=getDictDatasByEecel2(self.fpath)
        new_order_datas=[]
        db_map=DBO_MAP.get(tb_name)
        inverted_dict = {value: key for key, value in db_map.items()}
        for r in order_datas:
            cur_item={}
            for k,v in r.items():

                if '详细地址' in k:
                    adress_list=v.split('|')
                    if len(adress_list)==1:
                        adress_list.append(None)
                    cur_item['BuyerAdress1']=adress_list[0]
                    cur_item['BuyerAdress2']=adress_list[1]

                else:
                    if inverted_dict.get(k):
                        cur_item[inverted_dict[k]]=v
            new_order_datas.append(cur_item)
        return new_order_datas

    def upload_dd(self,row,pitem):

        params=list(pitem.values())
        pid=pitem['ChanPinID']
        zdstr=','.join(list(pitem.keys()))
        wstr=','.join(['?' for _ in params])
        sqlstr=f'insert into [AllegroOrder]({zdstr}) values({wstr})'
        aff=self.ms.ExecNoQuery(sqlstr,params)
        self.send_count-=1
        if aff>0:
            print(f'第{row}行,上传成功,剩余队列:{self.send_count}')
        else:
            print(f'第{row}行,上传失败,剩余队列:{self.send_count}')
        
        return aff

    def start_dr2(self):
        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先选择文件')
            return
        
        self.drbtn2.config(state=DISABLED)
        new_order_datas=self.get_new_data()
        self.send_count=len(new_order_datas)
        self.zytitle=['AllegroOrderid', 'ChanPinID', 'OrderAmount', 'BuyerName', 'BuyerPhone', 'BuyerCity', 
                      'BuyerAdress1', 'BuyerAdress2', 'YouBian']
        self.headers=['AllegroOrderid', 'ChanPinID', 'OrderAmount', 'BuyerName', 'BuyerPhone', 
                      'BuyerCity', 'BuyerAdress1', 'BuyerAdress2', 'YouBian', 'SMTUrl', 'SMTSku1', 'SMTSku2', 'SMTSku3', 
                      'SMTSku4', 'Quantity', 'CGAmount','KuaiDiJiaGe','OrderAmount','SMTOrderid','ProfitRatio', 'BuyerBianID', 
                      'BuyerAccount', 'OrderStatus'] 
        sufb=0
        sbfb=0
        task_rows=[]
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=1
            for r in new_order_datas:

                futures.append(executor.submit(self.create_Row,row,r))
                row+=1
            for future in concurrent.futures.as_completed(futures):
                try:
                    res_c=future.result()
                    if res_c:
                        task_rows.append(res_c)

                except Exception as exc:
                    print(f"线程出错: {exc}")
        
        task_rows.sort(key=lambda x: x[0])
        #print(task_rows)
        fname=f'allegro采购任务表_{getTimeStr()}.xlsx'
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.title = 'Sheet1'
        ws.append([DBO_MAP['AllegroOrder0'][tit] for tit in self.headers])
        
        for r,dataline in task_rows:
            ws.append(dataline)

        wb.save(fname)
        print(f'成功生成任务表:{len(task_rows)}行,到《{fname}》')
        messagebox.showinfo('提示',f'成功生成任务表:{len(task_rows)}行,到《{fname}》')
        self.drbtn2.config(state=NORMAL)
    
    def create_Row(self,row,pitem):
        oid=pitem['AllegroOrderid']
        rs0=self.ms.ExecQuery('select count(*) from AllegroOrder where AllegroOrderid=? and isCreateCg=1',(oid,))
        if rs0[0][0]>0:
            print(f'第{row}行,订单号:{oid},已经生成过采购任务,跳过')
            return None

        new_row={key: '' for key in self.headers}

        for k in self.zytitle:
            new_row[k]=pitem[k]
        pid=str(pitem['ChanPinID'])
        rs= self.ms.ExecQuery('select SMTUrl, SMTSku1, SMTSku2, SMTSku3, SMTSku4, Quantity from allproduct where ChanPinID=?',(pid,))
        if len(rs)>0:
            new_row['SMTUrl'],new_row['SMTSku1'],new_row['SMTSku2'],new_row['SMTSku3'],new_row['SMTSku4'],new_row['Quantity']=rs[0]
            print(f'第{row}行,匹配到总表产品ID,自动填充速卖通货源')
        else:
            print(f'第{row}行,无匹配到总表产品ID留空')
        self.ms.ExecNoQuery('update AllegroOrder set isCreateCg=1 where AllegroOrderid=?',(oid,))
        return [row,list(new_row.values())]
    
    def start_dr3(self):
        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先选择文件')
            return
        
        self.drbtn3.config(state=DISABLED)
        self.upzd=['SMTUrl','SMTSku1','SMTSku2','SMTSku3','SMTSku4','Quantity','CGAmount','KuaiDiJiaGe','OrderAmount','SMTOrderid',
                   'ProfitRatio','BuyerBianID','BuyerAccount','OrderStatus']
        new_order_datas=self.get_new_data(tb_name='AllegroOrder0')

        self.send_count=len(new_order_datas)
        sufb=0
        sbfb=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=1
            for r in new_order_datas:
                oid=r['AllegroOrderid']
                cur_params=[]
                for zd in self.upzd:
                    cur_params.append(r[zd])
                    
                futures.append(executor.submit(self.upload_cginfo,row,oid,cur_params))
                row+=1
            for future in concurrent.futures.as_completed(futures):
                try:
                    res_status=future.result()
                    if res_status:
                        sufb+=1
                    else:
                        sbfb+=1
                except Exception as exc:
                    print(f"线程出错: {exc}")

        print('开始更新波兰总产品表...')

        sqlstr='''UPDATE allproduct
                    SET 
                        smturl = AllegroOrder.smturl,
                        smtsku1 = AllegroOrder.smtsku1,
                        smtsku2 = AllegroOrder.smtsku2,
                        smtsku3 = AllegroOrder.smtsku3,
                        smtsku4 = AllegroOrder.smtsku4,
                        Quantity=AllegroOrder.Quantity,
                        smtprice=AllegroOrder.CGAmount
                    FROM allproduct
                    INNER JOIN AllegroOrder ON  allproduct.ChanPinID =AllegroOrder.ChanPinID ;
                '''
        aff=self.ms.ExecNoQuery(sqlstr)
        print(f'发布完成,共{len(new_order_datas)}个任务,其中成功上传:{sufb}个,失败{sbfb}个,自动填充匹配速卖通采购信息 {aff} 个')
        messagebox.showinfo('提示',f'发布完成,共{len(new_order_datas)}个任务,其中成功上传:{sufb}个,失败{sbfb}个,自动填充匹配速卖通采购信息 {aff} 个')
        self.drbtn3.config(state=NORMAL)
    
    def upload_cginfo(self,row,oid,cur_params):
        zdstr=','.join([f'{zd}=?' for zd in self.upzd])
        sqlstr=f'update AllegroOrder set {zdstr} where AllegroOrderid=?'
        aff=self.ms.ExecNoQuery(sqlstr,cur_params+[oid])
        self.send_count-=1
        if aff>0:
            print(f'第{row}行,上传采购信息成功,剩余队列:{self.send_count}')
        else:
            print(f'第{row}行,上传采购信息失败,剩余队列:{self.send_count}')
        
        return aff
         
class CopTaskFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.rowCount=IntVar()
        self.createWidgets()

    def createWidgets(self):
        
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='自动填充allegro跟卖任务表', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, text='导入任务表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.rowCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        self.drbtn=Button(f00,text='开始填充',command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green')
        self.drbtn.pack(side=LEFT, padx=2, pady=2)
    
    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.rowCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')

    def start_dr(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入文件')
            return

        self.drbtn.config(state=DISABLED)
        sqlstr='''
                SELECT a.pmbid, COUNT(*) as pcount
                FROM ALLproduct as a left join mb as b on a.pmbid=b.did  
                where a.pean is not null and a.pmbid<>-1 and a.pmbid<>0 and a.pgm=0 and a.isuse=0 and a.pw<>0 and b.dstate<>-1 and a.isuse=0 
                GROUP BY a.pmbid
                order by pcount desc
            '''
        rs=trydosql(self.ms,sqlstr)
        print(f'共获取到{len(rs)}个模版有产品,开始填充任务表的模版ID')
        wb=openpyxl.load_workbook(self.fpath)
        ws=wb[wb.sheetnames[0]]
        cur_row=2
        max_row=ws.max_row
        for pmbid,pcount in rs:
            first=1
            ccont=pcount
            while True:
                if (cur_row>max_row or ccont<2500) and first!=1:
                    break
                if ccont>=2500:
                    ws.cell(cur_row,12).value=pmbid
                    cur_row+=1
                    ccont-=2500
                    print(f'第{cur_row}成功填充模版ID:{pmbid},数量剩余:{ccont}')
                else:
                    ws.cell(cur_row,12).value=pmbid
                    cur_row+=1
                    ccont-=2500
                    print(f'第{cur_row}成功填充模版ID:{pmbid},数量剩余:{ccont if ccont>=0 else 0}')
                first+=1
            if cur_row>max_row:
                break
        wb.save(self.fpath)
        print('填充模版ID完毕')
        messagebox.showinfo('提示','填充模版ID完毕')

        self.drbtn.config(state=NORMAL)

class HYFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.rowCount=IntVar()
        self.dpcountstr=StringVar()
        self.dpcountstr.set('正在获取店铺数量...')
        Thread(target=self.getcount).start()
        self.createWidgets()

    def createWidgets(self):
        
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='查找货源', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)


        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, textvariable=self.dpcountstr,fg='red',width=15).pack(side=LEFT, padx=2, pady=2)
        self.dcbtn=Button(f00,text='导出需查找货源数据',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green')
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)


        Label(f00, text='已找货源表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.rowCount,fg='red',width=3).pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        self.drbtn=Button(f00,text='上传查找货源数据',command=lambda:Thread(target=self.start_dr).start(),fg='#ffffff',bg='green')
        self.drbtn.pack(side=LEFT, padx=2, pady=2)

    def getcount(self):

        while True:
            try:
                sqlstr='select count(*) from allshop where state=0 and smtcount<10'
                rs=self.ms.ExecQuery(sqlstr)
                self.dpcountstr.set(f'可导出店铺:{rs[0][0]}个')
            except Exception as e:
                print(e)

            time.sleep(20)
            

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.rowCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示','请选择excel文件')\
    

    def start_dc(self):



        self.dcbtn.config(state=DISABLED)
        #先更新店铺信息
        print(f'先更新店铺信息...')
        aff=ms.call_procedure('MergeAllshopData')
        print(f'成功更新 {aff} 个店铺信息')
        
        sql_pro='''
                select top 150
                    tp.shopid,
                    tp.purl ,
                    tp.pean ,
                    tp.SMTUrl	,
                    tp.SMTSku1	,
                    tp.SMTSku2	,
                    tp.SMTSku3	,
                    tp.SMTSku4	,
                    tp.Quantity ,
                    tp.SMTPrice
                from allproduct as tp 
                right join allshop as ts on tp.shopid=ts.shopid
                where tp.isuse=0 and tp.SMTUrl is null and ts.state=0 order by (ts.pcount-ts.smtcount)
                '''
        rspro= self.ms.ExecQuerydict(sql_pro)

        dpids=[r['shopid'] for r in rspro]
        wstr=','.join(['?' for _ in dpids])
        aff2= self.ms.ExecNoQuery(f'update allshop set state=1 where shopid in ({wstr})',dpids)
        print(f'成功将{aff2}个店铺标记为两天后导出')

        fname=crate_excel('波兰需查找货源数据',rspro,dbpmap=DBO_MAP['ALLproduct'])
        if fname:
            print(f'成功导出:{fname}')
            messagebox.showinfo('提示',f'成功导出:{fname}')
        else:
            print(f'导出失败')
            messagebox.showinfo('导出失败')
        self.dcbtn.config(state=NORMAL)

    def start_dr(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入货源表格')
            return
        
        self.drbtn.config(state=DISABLED)

        def up_hy(row,pitem):

            params=list(pitem.values())
            zdstr=','.join([f'{zd}=?' for zd in pitem.keys()])
            sqlstr=f'update allproduct set {zdstr} where isuse=0 and pean=?'
            aff=trydosql(self.ms,sqlstr,params+[pitem['pean']],type=2,force=1)

            return row,aff


        pdatas=getDictDatasByEecel2(self.fpath)
        tcount=len(pdatas)-1
        suc=0
        fail=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=50) as t0:

            tasks=[t0.submit(up_hy,j+2,pdatas[j]) for j in range(1,len(pdatas))]

            for t in concurrent.futures.as_completed(tasks):
                try:
                    res_row,res_aff=t.result()
                    tcount-=1
                    if res_aff:
                        suc+=1
                    else:
                        fail+=1
                    print(f'第 {res_row} 行,上传完毕,上传状态:{res_aff},剩 {tcount} 行')
                except Exception as e:
                    print(f'线程错误=>{e}')
        
        print(f'货源信息全部上传完毕,成功:{suc}个,失败:{fail}个')
        affdp=ms.call_procedure('MergeAllshopData')
        print(f'成功更新 {affdp} 个店铺信息')
        affpro=ms.call_procedure('UpdateNewSalePrice')
        print(f'成功更新 {affpro} 行产品价格')
        messagebox.showinfo('提示',f'货源信息全部上传完毕,成功:{suc}个,失败:{fail}个')
        self.drbtn.config(state=NORMAL)

# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('allgero')
# DataFra9(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()